package com.huatech.common.export;

import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import com.github.pagehelper.PageHelper;
import com.huatech.common.constant.ExportConstant;
import com.huatech.common.util.BeanMapUtils;
import com.huatech.common.util.EnumFieldUtils;

/**
 * excel 导出工具类
 * @author lh
 * @version 3.0
 * @since 2017-5-22
 *
 */
public class ExportUtil {
	
	private ExportUtil() {
	}
	
	public static void exportExcel(ExportModel exportModel,
			    final HttpServletRequest request, final HttpServletResponse response) throws IOException {
		if(exportModel == null ){
			throw new IllegalArgumentException("export model can not be null!");
		}
		
		String exportTitle = request.getParameter(ExportConstant.PARAM_EXPORT_TITLE);
		String[] headers = request.getParameter(ExportConstant.PARAM_HEARDERS).split(",");
		String[] fields  = request.getParameter(ExportConstant.PARAM_FIELDS).split(",");
		//create workbook 
		Workbook wb =  ExportUtil.createWorkbook();
		//总记录数
		int count = exportModel.getCount();
		if(count == 0){//查询结果为空
			Sheet sheet = wb.createSheet(exportTitle + "_1");
			sheet = wb.getSheetAt(0); 
			sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
			createHeader(sheet, exportTitle, headers);	
		}else{
			for (int i = 0, j = getTotalPage(count); i < j; i++) {
				PageHelper.startPage(i+1, ExportConstant.DEFAULT_EXPORT_PAGE_SIZE, false);
				ExportUtil.exportExcel(exportTitle, headers, fields, 
						i * ExportConstant.DEFAULT_EXPORT_PAGE_SIZE, wb, exportModel.getPageRecords());
			}			
		}
		ExportUtil.responseWorkbook(exportTitle, wb, request, response);
	}
	
	private static void exportExcel(String title, String[] headers, String[] fields, 
			int startRow, Workbook wb, List<?>  data) throws IOException {
		if(headers == null || headers.length == 0){
			throw new RuntimeException("export headers must not be null");
		}
		Sheet sheet = null;
		if(CollectionUtils.isEmpty(data)){
			if(startRow == 0){
				sheet = wb.createSheet(title + "_1");
				sheet = wb.getSheetAt(0); 
				sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
				createHeader(sheet, title, headers);
			}
			return ;
		}
		startRow = startRow>0?startRow+2:startRow;
		int index = startRow, pageRowNo = startRow, columnCount = headers.length; // 行号、页码、列数
		
		//枚举类型
		Map<String, String> jsonMap = EnumFieldUtils.json2Map(data.get(0));
		for (Object obj : data) {
			int sheetIndex = index/ExportConstant.SHEET_MAX_COUNT;
			if (index % ExportConstant.SHEET_MAX_COUNT == 0) {
				sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
				sheet = wb.getSheetAt(sheetIndex); 
				sheet.setDisplayGridlines(false);// 设置表标题是否有表格边框
				pageRowNo = 2; 
				createHeader(sheet, title, headers);				
			}else{
				sheet = wb.getSheetAt(sheetIndex); 
			}
			index++;
			@SuppressWarnings("unchecked")
			Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : BeanMapUtils.bean2Map(obj);	
			Row nRow = sheet.createRow(pageRowNo++); // 新建行对象	
			for (int j = 0; j < columnCount; j++) {
				Cell cell = nRow.createCell(j);
				String field = fields[j];
				Object val = map.get(field);
				if(jsonMap != null && jsonMap.containsKey(field)){
					Object valObj = map.get(field);
					val = jsonMap.get(String.format(EnumFieldUtils.FIELD_MAP_KEY, field, isNull(valObj)));
					if (val == null) {
						val = isNull(valObj);	
					}					
				}
				setCellValue(sheet, cell, val);
			}			
		}
		
	}
	
	/**
	 * responseWorkbook
	 * @param title
	 * @param wb
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	private static void responseWorkbook(String title, Workbook wb,HttpServletRequest request, HttpServletResponse response)throws IOException{
		String sFileName = title + ".xlsx";
		// 火狐浏览器导出excel乱码
		String agent = request.getHeader("User-Agent");
		// 判断是否火狐浏览器
		boolean isFirefox = agent != null && agent.contains("Firefox");
		if (isFirefox) {
			sFileName = new String(sFileName.getBytes("UTF-8"), "ISO-8859-1");
		} else {
			sFileName = URLEncoder.encode(sFileName, "UTF8");
		}
		response.setHeader("Content-Disposition", "attachment; filename=".concat(sFileName));
		response.setHeader("Connection", "close");
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  
		//response.setHeader("Content-Type", "application/vnd.ms-excel");
		wb.write(response.getOutputStream());
	}
	/**
	 * 设置单元格的值
	 * @param cell
	 * @param cellVal
	 */
	private static void setCellValue(Sheet sheet, Cell cell, Object cellVal){
		if(cellVal == null || String.class.equals(cellVal.getClass())){
			cell.setCellValue(filterHref(cellVal));
		}else if(Integer.class.equals(cellVal.getClass()) || int.class.equals(cellVal.getClass())){
			cell.setCellValue(Integer.valueOf(cellVal.toString()));
		}else if(Long.class.equals(cellVal.getClass()) || long.class.equals(cellVal.getClass())){
			cell.setCellValue(Long.valueOf(cellVal.toString()));
		}else if(Double.class.equals(cellVal.getClass()) || double.class.equals(cellVal.getClass())){
			cell.setCellValue(Double.valueOf(cellVal.toString()));
		}else if(Float.class.equals(cellVal.getClass()) || float.class.equals(cellVal.getClass())){
			cell.setCellValue(Float.valueOf(cellVal.toString()));
		}else if(BigDecimal.class.equals(cellVal.getClass())){
			cell.setCellValue(NumberUtils.toDouble(cellVal.toString()));
		}else if(Date.class.equals(cellVal.getClass())){
			cell.setCellValue(formatDateTime((Date)cellVal));
		}else if(Timestamp.class.equals(cellVal.getClass())){
			cell.setCellValue(formatDateTime((Timestamp)cellVal));
		}else{
			cell.setCellValue(isNull(cellVal));
		}
		cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(3));
	}
	

	/**
	 * 取得总页码数
	 * @param count
	 * @return
	 */
	private static int getTotalPage(int count){
		int pageSize = ExportConstant.DEFAULT_EXPORT_PAGE_SIZE;
		return (count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1);
	}
	
	/**
	 * 将对象转为字符串
	 * 
	 * @param o
	 * @return
	 */
	public static String isNull(Object o) {
		if (o == null) {
			return "";
		}
		String str;
		if (o instanceof String) {
			str = (String) o;
		} else {
			str = o.toString();
		}
		return str.trim();
	}
	
	/**
	 * 得到日期时间字符串，转换格式（yyyy-MM-dd HH:mm:ss）
	 */
	public static String formatDateTime(Date date) {
		return DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
	}
	
	/**
	 * 将对象转为字符串，去除超链接 
	 * @param o
	 * @return
	 */
	public static String filterHref(Object o) {
		if (o == null) {
			return "";
		}
		String str;
		if (o instanceof String) {
			str = (String) o;
		} else {
			str = o.toString();
		}
		return str.contains("href")?filterHtml(str):str;
	}
	
	/**
	 * 过滤html标签
	 * @param input
	 * @return
	 */
	public static String filterHtml(final String input){
		String result = input;
		Pattern phtml = Pattern.compile("<[^>]+>", Pattern.CASE_INSENSITIVE);
		Matcher mhtml = phtml.matcher(result);
		result = mhtml.replaceAll("");
		return result;
	}

	
	/**
	 * 创建表头
	 * @param sheet
	 * @param headers
	 */
	private static void createHeader(Sheet sheet, String title, String[] headers){
		
		//设置标题
		Row tRow = sheet.createRow(0);
		Cell hc = tRow.createCell(0);
		hc.setCellValue(new XSSFRichTextString(title));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));// 合并标题行：起始行号，终止行号， 起始列号，终止列号
		hc.setCellStyle(sheet.getWorkbook().getCellStyleAt(1));
		
		//设置表头
		Row nRow = sheet.createRow(1);
		for (int i = 0; i < headers.length; i++) {
			Cell cell =	nRow.createCell(i);
			cell.setCellValue(headers[i]);
			cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(2));
		}
	}
	
	
	/**
	 * 创建Workbook
	 * @return
	 */
	private static Workbook createWorkbook(){
		Workbook wb = new SXSSFWorkbook(100);
		CellStyle hcs = wb.createCellStyle();
		hcs.setBorderBottom(BorderStyle.THIN);
		hcs.setBorderLeft(BorderStyle.THIN);
		hcs.setBorderRight(BorderStyle.THIN);
		hcs.setBorderTop(BorderStyle.THIN);
		hcs.setAlignment(HorizontalAlignment.CENTER);
		Font hfont = wb.createFont();
		hfont.setFontName("宋体");
		hfont.setFontHeightInPoints((short) 16);// 设置字体大小
		hfont.setBold(true);// 加粗
		hcs.setFont(hfont);
		
		CellStyle tcs = wb.createCellStyle();
		tcs.setBorderBottom(BorderStyle.THIN);
		tcs.setBorderLeft(BorderStyle.THIN);
		tcs.setBorderRight(BorderStyle.THIN);
		tcs.setBorderTop(BorderStyle.THIN);
		Font tfont = wb.createFont();
		tfont.setFontName("宋体");
		tfont.setFontHeightInPoints((short) 12);// 设置字体大小
		tfont.setBold(true);// 加粗
		tcs.setFont(tfont);
		
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		
		return wb;
	}
	
	public static void main(String[] args) {
		Workbook wb =  ExportUtil.createWorkbook();
		System.out.println(wb);
	}
	
}
